package edu.westga.movierec.model;
import java.sql.*;
import java.util.ArrayList;
/**
 * This class opens a connection to the xampp mysql
 * database jcook hosted on Jean's Flash Drive
 * 
 * @author jmcook
 *
 */
public class XamppDBConnector{
	private Connection conn;
	/**
	 * Creates a new connection to xampp mysql
	 * running on Jean Cooks's flash drive
	 * <p>
	 * Precondition: XAMPP/MYSQL is running
	 * <p>
	 * Postcondition: isConnectionOpen() == true
	 */
	public XamppDBConnector() {
		try
		{
		// Step 1: Load the JDBC driver. 
	      Class.forName("com.mysql.jdbc.Driver");
	      // Step 2: Establish the connection to the database. 
	      String url = "jdbc:mysql://localhost/jcook"; 
	      conn = DriverManager.getConnection(url,"root","853759");
	    }
	    catch (Exception e)
	    {
	      e.printStackTrace();
	      System.err.println(e.getMessage()); 
	    }
	  }
	/**
	 * Checks if the connection is open
	 * 
	 * @return true if the connection is open and working
	 */
	public boolean isConnectionOpen(){
		try {
			if(this.conn.isValid(3))
			{
				return true;
			}
			return false;
		} catch (SQLException e) {
			return false;
		}
	}
	/**
	 * Returns a recommendation based on
	 * a user's movie choice
	 * <p>
	 * Precondition: userID > 0 && getmovieIndex(movieTitle) > 0 && isConnectionOpen()==true
	 * 
	 * @return: the title of the recommended movie
	 */
	public String recommend(int userID, String movieTitle){
		try{
			int movieID = getMovieIndex(movieTitle);
			Statement s = conn.createStatement();
			ResultSet recs = s.executeQuery(
					"SELECT output FROM ratings WHERE input="+movieID+" ORDER BY rating DESC");
			while(recs.next())
			{
				int movieRec = recs.getInt("output");
				if(!isAlreadyRecommended(userID,movieRec))
				{
					addMovieToUserHistory(userID, getMovieTitle(movieRec));
					return getMovieTitle(movieRec);
				}
			}
			return "All movies are recommended.";
		}
		catch(Exception e)
		{
			return "Database not found.";
		}
	}
	//************Database Accessor Methods*******************************
	/**
	 * Returns the number the database uses to identify the movie
	 * <p>
	 * Precondition: movieTitle != null && isConnectionOpen()==true
	 * <p<
	 * @return the movie ID number or 0 if not found
	 */
	public int getMovieIndex(String movieTitle){
		try{
			Statement s = conn.createStatement();
			ResultSet r = s.executeQuery(
					"SELECT id FROM movies WHERE title='"+movieTitle+"'");
			if(r.next())
			{
				return r.getInt("id");
			}
			return 0;
		}
		catch(Exception e)
		{
			return -1;
		}
	}
	/**
	 * Returns the movie corresponding to an ID
	 * <p>
	 * Precondition: movieIndex > 0 && isConnectionOpen()==true
	 * <p>
	 * @param movieIndex
	 * @return the movie title or "not found"
	 */
	public String getMovieTitle(int movieIndex){
		try{
			Statement s = conn.createStatement();
			ResultSet r = s.executeQuery(
					"SELECT title FROM movies WHERE id="+movieIndex);
			if(r.next())
			{
				return r.getString("title");
			}
	    	return "Movie not found";	
		}
		catch(Exception e)
		{
			return "Database not found";
		}
	}
	/**
	 * Returns the number of users who have liked 
	 * the output when they inserted the input
	 * <p>
	 * Precondition: getMovieIndex(input) > 0 && getMovieIndex(output) > 0 && isConnectionOpen()==true
	 * @return rating
	 */
	public int getMovieRating(String input, String output){
		try{
			Statement s = conn.createStatement();
			ResultSet r = s.executeQuery(
					"SELECT * FROM ratings WHERE input="+getMovieIndex(input)+" and output="+getMovieIndex(output));
	    	if(r.next())
	    	{
	    		return r.getInt("rating");
	    	}
	    	else
	    	{
	    		return -1;
	    	}
		}
		catch(Exception e)
		{
			return -1;
		}
	}
	/**
	 * Returns true if the movie has already been
	 * recommended to the user
	 * <p>
	 * Precondition: user > 0 && getMovieTitle(movieID) != "Movie not found" && isConnectionOpen()==true
	 * <p>
	 * @return true if the movie has been recced for that user
	 */
	public boolean isAlreadyRecommended(int userID, int movieID){
		try{
			Statement s = conn.createStatement();
			ResultSet r = s.executeQuery(
					"SELECT * FROM history WHERE user="+userID+" and movie="+movieID);
	    	return r.next();
		}
		catch(Exception e)
		{
			return false;
		}
	}
	//*****************Database Response Handlers******************
	/**
	 * Increase the rating of a movie by one point if the user liked it
	 * <p>
	 * Precondition: getMovieIndex(input) > 0 && getMovieIndex(output) > 0 && isConnectionOpen()==true
	 * <p>
	 * Postcondition: if liked==true, getMovieRating(input,output) == getMovieRating(input,output)@pre+1
	 * 				else, getMovieRating(input,output) == getMovieRating(input,output)@pre-1
	 */
	public void userResponse(String input, String output,boolean liked)
	{
		try{
			int newRating = getMovieRating(input,output);
			if(liked){
				newRating++;
			}
			else{
				newRating--;
			}
	    	setMovieRating(input,output,newRating);
		}
		catch(Exception e)
		{
		}
	}
	//*****************************Database Mutators************************
	/**
	 * Resets a user's history by deleting all the user's rows
	 * from the history table
	 * <p>
	 * Precondition: isConnectionOpen()==true && userID > 0
	 * <p>
	 * Postcondition: table history.user != userID
	 */
	public void resetUser(int userID){
		try{
			Statement s = conn.createStatement();
			s.executeUpdate("DELETE FROM history WHERE user="+userID);
		}
		catch(Exception e)
		{
		}
	}
	
	/**
	 * Deletes all references to a given movie from the entire database
	 * <p>
	 * Precondition: isConnectionOpen()==true
	 * <p>
	 * Postcondition: getMovieID(movieTitle) == 0 
	 * @param movieTitle
	 */
	public void deleteMovieFromDatabase(String movieTitle){
		try{
			int movieID = getMovieIndex(movieTitle);
			//If movie not there, do nothing
			if (movieID <= 0){
				return;
			}
			Statement s = conn.createStatement();
			s.executeUpdate("DELETE FROM history WHERE movie="+movieID);
			s.executeUpdate("DELETE FROM ratings WHERE input="+movieID);
			s.executeUpdate("DELETE FROM ratings WHERE output="+movieID);
			s.executeUpdate("DELETE FROM movies WHERE id="+movieID);
		}
		catch(Exception e)
		{}
	}
	/**
	 * Adds movie to appropriate tables in database
	 * <p>
	 * Precondition: isConnectionOpen()==true && movieTitle != "" && movieTitle != null
	 * <p>
	 * Postcondition: getMovieIndex == 0 && getMovieRating(title, movieTitle) == 0
	 * 					&& getMovieRating(movieTitle, title) == 0
	 * @param movieTitle
	 */
	public void addMovieToDatabase(String movieTitle){
		try{
			//If movie already exists, do nothing
			if(getMovieIndex(movieTitle)!=0){
				return;
			}
			Statement s = conn.createStatement();
			s.executeUpdate("INSERT INTO movies VALUES (null, '"+movieTitle+"')");
			int movieID = getMovieIndex(movieTitle);
			for(int i=1;i<movieID;i++){
				if(!(getMovieTitle(i)=="Movie not found")){
					s.executeUpdate("INSERT INTO ratings VALUES ("+i+", "+movieID+", 0)");
					s.executeUpdate("INSERT INTO ratings VALUES ("+movieID+", "+i+", 0)");
				}
			}
		}
		catch(Exception e)
		{}
	}
	/**
	 * Deletes a specific movie title from a user's history, so that
	 * movie can be recommended again.
	 * <p>
	 * Precondition: isConnectionOpen()==true && userID > 0
	 * <p>
	 * Postcondition: isAlreadyRecommended(userID, movieTitle)==false
	 * <P>
	 * @param userID
	 * @param movieTitle
	 */
	public void deleteMovieFromUserHistory(int userID, String movieTitle){
		try{
			Statement s = conn.createStatement();
			s.executeUpdate("DELETE FROM history WHERE user="+userID+" and movie="+getMovieIndex(movieTitle));
		}
		catch(Exception e)
		{
		}
	}
	/**
	 * Adds a particular movie to a user history
	 * <P>
	 * Precondition: userID > 0 && getMovieIndex(movieTitle) > 0 && isConnectionOpen == true
	 * <p>
	 * Postcondition: isAlreadyRecommended(userID, getMovieIndex(movieTitle))==true
	 * @param userID
	 * @param movieTitle
	 */
	public void addMovieToUserHistory(int userID, String movieTitle){
		try{
			Statement s = conn.createStatement();
			s.executeUpdate("INSERT INTO history VALUES ("+userID+", "+getMovieIndex(movieTitle)+")");
		}
		catch(Exception e)
		{}
	}
	/**
	 * Changes the ratings between two particular movies to the users preference
	 * <p>
	 * Precondition: isConnectionOpen() == true && getMovieRating(input, output) !=-1
	 * @param input
	 * @param output
	 * @param newRating
	 */
	public void setMovieRating(String input, String output, int newRating){
		try{
			Statement s = conn.createStatement();
			s.executeUpdate("UPDATE ratings SET rating="+newRating+
	    			" WHERE input="+getMovieIndex(input)+" and output="+getMovieIndex(output));
		}
		catch(Exception e)
		{}
	}
	//*************************GUI Accessor Methods**********************
	/*
	 * Pulls all the movies and ratings from the ratings table that correspond
	 * to a particular input movie and returns them as an array of String arrays
	 * <p>
	 * Precondition: isConnectionOpen == true and getMovieIndex(input)>0
	 * <p>
	 * Returns String[][]
	 */
	public String[][] getMovieRecs(String input){
		try{
			 
			Statement s = conn.createStatement();
			ResultSet r = s.executeQuery("SELECT * FROM ratings WHERE input="+getMovieIndex(input)+" ORDER BY rating DESC");
			r.last();
			int limit = r.getRow();
			r.beforeFirst();
			String[][] movieArray = new String[limit][2];
			int cnt = 0;
			while(r.next() && cnt<=limit){
				String movie = getMovieTitle(r.getInt("output"));
				String rating = Integer.toString(r.getInt("rating"));;
				String[] titleArray = {movie,rating};
				movieArray[cnt]= titleArray;
				cnt++;
			}
			return movieArray;
		}
		catch(Exception e)
		{
			String[][] movieArray = {{"Database Down",""}};
			return movieArray;
		}
	}
	/*
	 * Pulls a particular user's history and returns 
	 * them as an array of Strings
	 * <p>
	 * Precondition: isConnectionOpen == true and userID > 0
	 * <p>
	 * Returns String[]
	 */
	public String[] getUserHistory(int userID){
		try{
			Statement s = conn.createStatement();
			ResultSet r = s.executeQuery("SELECT * FROM history WHERE user="+userID);
			r.last();
			int limit = r.getRow();
			r.beforeFirst();
			String[] userArray = new String[limit];
			int cnt = 0;
			while(r.next() && cnt<=limit){
				int movieID = r.getInt("movie");
				userArray[cnt] = getMovieTitle(movieID);
				cnt++;
			}
			return userArray;
		}
		catch(Exception e)
		{
			String[] movieArray = {"Database Down"};
			return movieArray;
		}
	}
	

	
}
